/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package packageDBAccess;


import java.sql.*;
import packageException.*;
import packageModel.*;
import java.util.*;
import java.util.logging.Level;
import java.util.logging.Logger;
import packageController.ServletDescription;

/**
 *
 * @author etu17691
 */
public class DBAccess
{ 
    public void addUser(Utilisateur u) throws ClassNotFoundException,RequetteException, ConnectionDBException
    {
        try
        {
                DBConnection dbc = new DBConnection();
                String requeteSQL = "INSERT INTO Utilisateur (Nom, Prenom, Adresse, CodePostal, Ville, Email, Mdp) VALUES (?, ?, ?, ?, ?, ?, ?)";
                PreparedStatement prepStat = dbc.getInstance().prepareStatement(requeteSQL);
                prepStat.setString(1, u.getNom());
                prepStat.setString(2, u.getPrenom());
                prepStat.setString(3, u.getAdresse());
                prepStat.setInt(4, u.getCodePostal());
                prepStat.setString(5, u.getVille());
                prepStat.setString(6, u.getEmail());
                prepStat.setString(7, u.getMdp());
                prepStat.executeUpdate();

                if(!u.getTel().isEmpty())
                {
                        requeteSQL="update UTILISATEUR set TELEPHONE = ? WHERE EMAIL = '"+u.getEmail()+"'";
                        prepStat = dbc.getInstance().prepareStatement(requeteSQL);
                        prepStat.setString(1,u.getTel());
                        prepStat.executeUpdate();
                }
                dbc.getInstance().close();
        }
        catch(SQLException ex)
        {throw new RequetteException ();}
        
    }
    
    public ArrayList<Categorie> getCatList() throws ResultSetException, ConnectionDBException, ClassNotFoundException
    {  
       ArrayList<Categorie> liste = new ArrayList<Categorie>();        
        
        try
        {
            DBConnection dbc = new DBConnection();
            String requeteSQL ="Select * FROM CATEGORIE";
            PreparedStatement p = dbc.getInstance().prepareStatement(requeteSQL);
            ResultSet donnees = p.executeQuery();
            
            while (donnees.next())
            {
                liste.add(new Categorie(new Integer(donnees.getInt(1)),donnees.getString(2),donnees.getString(2)));
            }
            
            dbc.getInstance().close();
        }
        catch(SQLException se)
        {throw new ResultSetException("Impossible de récupérer les Catégories");}
        
        return liste;
    }
   
    public ArrayList<Livre> getBookListCat(int cat) throws ResultSetException, ClassNotFoundException, ConnectionDBException
    {
        DBConnection dbc = new DBConnection();
        ArrayList<Livre> bookCatList = new ArrayList<Livre>();
        
        try
        {
            String requeteSQL ="select L.ISBN, L.TITRE, L.RESUME, L.PRIX, A.NOM, A.PRENOM, C.LIBELLE, P.POURCENTAGE from Livre L JOIN CATEGORIE C ON L.DAN_ID = C.ID JOIN AUTEUR A ON L.ID = A.ID LEFT OUTER JOIN PLIV Z ON L.ISBN = Z.ISBN LEFT OUTER JOIN PROMOTION P ON P.ID =Z.ID  where L.DAN_ID = ?";
            PreparedStatement p = dbc.getInstance().prepareStatement(requeteSQL);
            p.setInt(1, cat);
            ResultSet donnees = p.executeQuery();
            
            while (donnees.next())
            {
               
                    bookCatList.add(new Livre(donnees.getLong(1),donnees.getString(2),donnees.getString(3),new Double(donnees.getFloat(4)),donnees.getString(5),donnees.getString(6),donnees.getString(7),donnees.getInt(8)));
                

            }
            dbc.getInstance().close();  
        }
        catch(SQLException ex)
        {throw new ResultSetException(ex.getMessage());}
        
        return bookCatList;
    }
    
    public Livre getBook(long isbn) throws ResultSetException, ClassNotFoundException, ConnectionDBException
    {
        DBConnection dbc = new DBConnection();
        Livre book = new Livre();
        
        try
        {
            String requeteSQL = "select L.ISBN, L.TITRE, L.RESUME, L.PRIX, A.NOM, A.PRENOM, C.LIBELLE, P.POURCENTAGE from Livre L JOIN CATEGORIE C ON L.DAN_ID = C.ID JOIN AUTEUR A ON L.ID = A.ID LEFT OUTER JOIN PLIV Z ON L.ISBN = Z.ISBN LEFT OUTER JOIN PROMOTION P ON P.ID =Z.ID where L.ISBN = ?";
            PreparedStatement p = dbc.getInstance().prepareStatement(requeteSQL);
            p.setLong(1, isbn);
            ResultSet donnees = p.executeQuery();
            while(donnees.next())
            {
  
                    book = new Livre(donnees.getLong(1),donnees.getString(2),donnees.getString(3),new Double(donnees.getFloat(4)),donnees.getString(5),donnees.getString(6),donnees.getString(7),donnees.getInt(8));
     
            }
            dbc.getInstance().close();
        }
        catch(SQLException se)
        {throw new ResultSetException("Impossible d'afficher ce livre");}
        
        return book;
    }
    
    public ArrayList<Livre>getLivreRecherche(String rech) throws ConnectionDBException, ClassNotFoundException
    {
        DBConnection dbc = new DBConnection();
        ArrayList<Livre>liste = new ArrayList<Livre>();
        String recherche ="%";
        recherche += rech+"%";
        
        try
        {
            String requeteSQL ="select L.ISBN, L.TITRE, L.RESUME, L.PRIX, A.NOM, A.PRENOM, C.LIBELLE, P.POURCENTAGE from Livre L JOIN CATEGORIE C ON L.DAN_ID = C.ID JOIN AUTEUR A ON L.ID = A.ID LEFT OUTER JOIN PLIV Z ON L.ISBN = Z.ISBN LEFT OUTER JOIN PROMOTION P ON P.ID =Z.ID where upper(L.Titre) LIKE upper(?)";
            PreparedStatement p = dbc.getInstance().prepareStatement(requeteSQL);
            p.setString(1, recherche);
            ResultSet donnees = p.executeQuery();

            while(donnees.next())
            {

                    liste.add(new Livre(donnees.getLong(1),donnees.getString(2),donnees.getString(3),new Double(donnees.getFloat(4)),donnees.getString(5),donnees.getString(6),donnees.getString(7),donnees.getInt(8)));
 
            }
            dbc.getInstance().close();
        }
        catch(SQLException ex)
        {Logger.getLogger(ServletDescription.class.getName()).log(Level.SEVERE, null, ex);}
        catch(Exception ex)
        {Logger.getLogger(ServletDescription.class.getName()).log(Level.SEVERE, null, ex);}
        
        return liste;
    }
   
    public Utilisateur getUserByMail(String mail) throws ClassNotFoundException, ConnectionDBException
    {   
        DBConnection dbc = new DBConnection();
        Utilisateur u = new Utilisateur();
        
        try
        {   
            String requeteSQL = "SELECT * FROM UTILISATEUR WHERE EMAIL = ?";
            PreparedStatement p = dbc.getInstance().prepareStatement(requeteSQL);
            p.setString(1, mail);
            ResultSet donnees = p.executeQuery();
            
            while (donnees.next())
            {
                u = new Utilisateur(donnees.getInt(1), donnees.getString(7), donnees.getString(9), donnees.getString(2), donnees.getString(3), donnees.getString(4), donnees.getString(6), donnees.getInt(5), donnees.getString(8));
            }
            
            dbc.getInstance().close();
            
        }
        catch (SQLException ex)
        {
            Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
        }
        return u;
    }  
    
        public Integer getLastCom(Integer id) throws ConnectionDBException, ClassNotFoundException{
        Integer lastCom = null;
            
            try {            
            DBConnection dbc = new DBConnection();
            
            String requeteSQL = "SELECT max(NUMCOM) FROM COMMANDE WHERE ID = ?";
            PreparedStatement p = dbc.getInstance().prepareStatement(requeteSQL);
            p.setInt(1, id);
            ResultSet donnees = p.executeQuery();
            
            
            
            while(donnees.next()){
                
                lastCom = donnees.getInt(1);
                
            }
                
            
            
            
            
        } catch (SQLException ex) {
            Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
        }
            return lastCom;
            
        }
    
        public void addCommande(Integer id, Calendar cal, Integer numCom) throws ConnectionDBException, ClassNotFoundException, ResultSetException{
        try {
            DBConnection dbc = new DBConnection();
            
            java.sql.Date dateSQL = new java.sql.Date(cal.getTime().getTime());
            
           String requeteSQL = "INSERT INTO COMMANDE (ID, NUMCOM, DATE) VALUES (?,?,?)";
           PreparedStatement p = dbc.getInstance().prepareStatement(requeteSQL);
           p.setInt(1, id);
           p.setInt(2, numCom);
           p.setDate(3, dateSQL);
           p.executeUpdate();
           
           dbc.getInstance().close();
           
        } catch (SQLException ex) {
            throw new ResultSetException("Impossible d'ajouter une nouvelle commande");}
        
    }
    
    public void addLigne(Integer numCom, Integer id, Long isbn, Integer qte, Double prix) throws ConnectionDBException, ClassNotFoundException, ResultSetException
    {
        DBConnection dbc = new DBConnection();
        try {
            String requeSQL = "INSERT INTO LIGNE (NUMCOM, ID, ISBN, QTE, PRIX) VALUES (?,?,?,?,?)";
            PreparedStatement p = dbc.getInstance().prepareStatement(requeSQL);
            p.setInt(1, numCom);
            p.setInt(2, id);
            p.setLong(3, isbn);
            p.setInt(4, qte);
            p.setDouble(5, prix);
            p.executeUpdate();
            
            dbc.getInstance().close();
            
        }
        catch (SQLException ex)
        {
            throw new ResultSetException("Impossible d'insérer cette ligne");
        }
    }
    

    
    
    
    public Commande getCom(Integer id, Integer numCom) throws ConnectionDBException, ClassNotFoundException, ResultSetException{
         Commande c = null;
         Calendar cal=Calendar.getInstance();  
            
            try {            
            DBConnection dbc = new DBConnection();
            
            String requeteSQL = "SELECT * FROM COMMANDE WHERE ID = ? AND NUMCOM = ?";
            PreparedStatement p = dbc.getInstance().prepareStatement(requeteSQL);
            p.setInt(1, id);
            p.setInt(2, numCom);
            ResultSet donnees = p.executeQuery();
            
            
            
            while(donnees.next()){
                
                cal.setTime(donnees.getDate(3));
                c = new Commande(donnees.getInt(1), donnees.getInt(2), cal);
                
            }
                
            
            
            
            
        } catch (SQLException ex) {
            throw new ResultSetException("impossible d'afficher votre commande");
        }
            return c;
            
        }
    
    public Commande getLignesCom (Commande c) throws ConnectionDBException, ConnectionDBException, ClassNotFoundException, ResultSetException{
        
        try {
            DBConnection dbc = new DBConnection();
                
               String requeteSQL = "SELECT L.ISBN, B.TITRE, A.NOM, L.PRIX, L.QTE  FROM LIGNE L, LIVRE B, AUTEUR A WHERE L.ID = ? AND L.NUMCOM = ? AND L.ISBN = B.ISBN AND B.ID =A.ID";
                PreparedStatement p = dbc.getInstance().prepareStatement(requeteSQL);
                p.setInt(1, c.getNumCli());
                p.setInt(2, c.getNumCom());
                ResultSet donnees = p.executeQuery();
                
                while (donnees.next()){
                    
                    c.addLigne(donnees.getLong(1), donnees.getString(2), donnees.getString(3), donnees.getDouble(4), donnees.getInt(5));
                    
                }
                return c;
                
                
        } catch (SQLException ex) {
            throw new ResultSetException("Impossible d'afficher votre commande");
        }
        
        
    }
    
     public ArrayList<Livre> getPromoLivre() throws ConnectionDBException, ClassNotFoundException, ResultSetException
    {
        DBConnection dbc = new DBConnection();
        ArrayList<Livre> bookCatList = new ArrayList<Livre>();
        
        try
        {
            String requeteSQL ="select L.ISBN, L.TITRE, L.RESUME, L.PRIX, A.NOM, A.PRENOM, C.LIBELLE, P.POURCENTAGE  from Livre L JOIN CATEGORIE C ON L.DAN_ID = C.ID JOIN AUTEUR A ON L.ID = A.ID JOIN PLIV Z ON L.ISBN = Z.ISBN JOIN PROMOTION P ON P.ID =Z.ID where CURRENT_DATE >= P.DATEDEB and CURRENT_DATE <= P.DATEFIN";
            PreparedStatement p = dbc.getInstance().prepareStatement(requeteSQL);
            ResultSet donnees = p.executeQuery();
            
            while (donnees.next())
            {

                    bookCatList.add(new Livre(donnees.getLong(1),donnees.getString(2),donnees.getString(3),new Double(donnees.getFloat(4)),donnees.getString(5),donnees.getString(6),donnees.getString(7),donnees.getInt(8)));
  
            }
            dbc.getInstance().close();  
        }
        catch(SQLException ex)
        {throw new ResultSetException(ex.getMessage());}
        
        return bookCatList;
    }
     public Boolean checkPromo(Long isbn) throws ClassNotFoundException, ConnectionDBException, ResultSetException{
        
         boolean bo = true;
         try {
            DBConnection dbc = new DBConnection();
            
            String requeteSQL = "select L.ISBN, L.TITRE, L.RESUME, L.PRIX, A.NOM, A.PRENOM, C.LIBELLE, P.POURCENTAGE  from Livre L JOIN CATEGORIE C ON L.DAN_ID = C.ID JOIN AUTEUR A ON L.ID = A.ID JOIN PLIV Z ON L.ISBN = Z.ISBN JOIN PROMOTION P ON P.ID =Z.ID where CURRENT_DATE >= P.DATEDEB and CURRENT_DATE <= P.DATEFIN AND L.ISBN = ? ";
            PreparedStatement p = dbc.getInstance().prepareStatement(requeteSQL);
            p.setLong(1, isbn);
            ResultSet donnees = p.executeQuery();           
            
            if(donnees.next()){
              
                  bo = true; 
            }
            else {
                bo = false;
            }
            
        } catch (SQLException ex) {
            throw new ResultSetException("impossible de vérifier la promo");
        }
        return bo;
     }
    
}
